using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using System.Text;
using GeneratorLibrary.Client.Base;
using System.Data.Common;

namespace MySqlGeneratorLibrary {
    public class MySqlDAC : BaseDAC {
        protected MySqlConnection connection {
            get { return (MySqlConnection)conn; }
        }
        public MySqlDAC(string strConnection, string database)
            : base(new MySqlConnection(strConnection), database) {
        }
        protected override DbCommand NewCommand(string strSql, DbParameter[] parameters) {
            MySqlCommand cmd = new MySqlCommand(strSql, connection);
            AddParams(parameters, cmd);
            return cmd;
        }

        private static void AddParams(DbParameter[] parameters, MySqlCommand cmd) {
            if (parameters != null) {
                foreach (MySqlParameter p in parameters)
                    cmd.Parameters.Add(p);
            }
        }

        public override DataTable GetColumns(string tableOrViewLike) {





            string strSql = @"select 
                    o.name as TableName, 
                    c.name as ColumnName,
                    COLUMNPROPERTY(o.id,c.name,'IsIdentity' ) isIdentity,
                    case when p.Column_Name is not null then 1 else 0 end as IsPrimaryKey,
                    case when f.Column_Name is not null then 1 else 0 end as IsForeignKey,
                    c.colorder as ColumnOrder,
                    t.name AS DataType,
                    c.IsNullable,
                    case when i.id is not null then 1 else 0 end as IsUniqueConstraint,
		            c.length, 
                    c.xprec as NumericPrecision, 
                    c.xscale as NumericScale, 
                    c.prec as CharMaxLength
                from 
                    syscolumns c 
                    inner join sysobjects o 
                        on c.id=o.id
                    INNER JOIN systypes t 
                        ON c.xtype = t.xtype  
                    left join (
                        SELECT c.Table_Name, k.Column_Name
                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
                        inner JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON k.table_name = c.table_name AND k.table_schema = c.table_schema AND k.table_catalog = c.table_catalog AND k.constraint_catalog = c.constraint_catalog AND k.constraint_name = c.constraint_name
                        where constraint_type = 'PRIMARY KEY'
                    ) p 
                        on o.Name = p.Table_Name and c.Name = p.Column_Name
                    left join (
                        SELECT c.Table_Name, k.Column_Name
                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
                        inner JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON k.table_name = c.table_name AND k.table_schema = c.table_schema AND k.table_catalog = c.table_catalog AND k.constraint_catalog = c.constraint_catalog AND k.constraint_name = c.constraint_name
                        where constraint_type = 'FOREIGN KEY'
                    ) f 
                        on o.Name = f.Table_Name and c.Name = f.Column_Name

                    left join (select id,indid from dbo.sysindexes where indid > 0 and indid < 255 and (status & 2)<>0) i 
		                on i.id = o.id 
                        and index_col(o.name,indid, 1) = c.name 
                        and index_col(o.name,indid, 2) is null
                where 
                    o.xtype in ('U','V') and 
                    o.name <> 'dtproperties' 
                    and t.name <> 'sysname' 
                    and o.name like '" + tableOrViewLike + @"'
                order by o.name,c.colorder";

            return runSql(strSql, null);
        }

        public override List<string> GetDatabases() {
            List<string> list = new List<string>();
            string excludedNames = "'master','model','msdb','tempdb'";
            MySqlCommand command = new MySqlCommand("SELECT name FROM sysdatabases where name not in (" + excludedNames + ") order by name", connection);
            using (IDataReader reader = command.ExecuteReader()) {
                while (reader.Read()) {
                    list.Add(reader.GetString(0));
                }
            }
            return list;
        }

        public override void Execute(string sql, DbParameter[] parameters) {
            NewCommand(sql, parameters).ExecuteNonQuery();
        }
        private DataTable runSql(string strSql, DbParameter[] parameters) {
            MySqlDataAdapter adapter = new MySqlDataAdapter((MySqlCommand)NewCommand(strSql, parameters));
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt = ds.Tables[0];
            return dt;
        }

        public override string GetInserts(string tableLike, bool disableIdentity) {
            StringBuilder sb = new StringBuilder();
            DataTable allTables = runSql("select o.name as TableName from sysobjects o where o.xtype='U' and o.name <> 'dtproperties' and o.name like '" + tableLike + "'", null);
            string identityString = "";
            if (!disableIdentity)
                identityString = " and COLUMNPROPERTY(o.id,c.name,'IsIdentity' ) = 0 ";
            foreach (DataRow tableRow in allTables.Rows) {
                string table = tableRow[0].ToString();
                if (disableIdentity)
                    sb.Append("set identity_insert " + table + " on" + Environment.NewLine);
                string columnList = "";
                DataTable columnTable = runSql("select c.name as ColumnName, c.colorder as ColumnOrder, case when t.name in ('bigint','binary','decimal','float','int','smallint','tinyint','varbinary') then 0 when t.name='bit' then -1 else 1 end as NeedsQuotes, COLUMNPROPERTY(o.id,c.name,'IsIdentity' ) as isIdentity from syscolumns c inner join sysobjects o on c.id=o.id INNER JOIN systypes t ON c.xtype = t.xtype left join (SELECT c.Table_Name, k.Column_Name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c inner JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON k.table_name = c.table_name AND k.table_schema = c.table_schema AND k.table_catalog = c.table_catalog AND k.constraint_catalog = c.constraint_catalog AND k.constraint_name = c.constraint_name where constraint_type = 'PRIMARY KEY' ) p on o.Name = p.Table_Name and c.Name = p.Column_Name where o.xtype='U' and t.name <> 'sysname' and o.name = '" + table + "' " + identityString + " order by o.name,c.colorder", null);
                foreach (DataRow row in columnTable.Rows) {
                    columnList += "," + row["ColumnName"];
                }
                columnList = columnList.Remove(0, 1);//remove initial comma

                DataTable dt2 = runSql("select " + columnList + " from " + table, null);

                foreach (DataRow row in dt2.Rows) {
                    sb.Append("insert into " + table + "(" + columnList + ") values (");
                    for (int i = 0; i < columnTable.Rows.Count; i++) {
                        if (i > 0) sb.Append(",");

                        if (row.IsNull(i)) {
                            sb.Append("null");
                        } else if ((int)columnTable.Rows[i]["NeedsQuotes"] == 1) {
                            sb.Append("'");
                            sb.Append(row[i].ToString().Replace("'", "''"));
                            sb.Append("'");
                        } else if ((int)columnTable.Rows[i]["NeedsQuotes"] == -1) {
                            sb.Append(((bool)row[i]) ? 1 : 0);
                        } else {
                            sb.Append(row[i].ToString());
                        }
                    }
                    sb.Append(")" + Environment.NewLine);
                }
                if (disableIdentity)
                    sb.Append("set identity_insert " + table + " off" + Environment.NewLine);
            }
            return sb.ToString();
        }
        public override List<string> GetTables() {
            string strSql = @"select o.name as TableName 
                from sysobjects o where o.xtype='U' and o.name <> 'dtproperties'
                order by o.name";
            return GetList(strSql, null);
        }
        public override List<string> GetViews() {
            string strSql = @"select o.name as TableName 
                from sysobjects o where o.xtype='V' and o.name not in ('sysconstraints','syssegments')
                order by o.name";
            return GetList(strSql, null);
        }
        public override List<string> GetProcs() {
            string strSql = "select name as procName from sysobjects where xtype='P' and substring(name,1,3) not in ('dt_','sp_') order by procName";
            return GetList(strSql, null);
        }
        public override List<string> GetProcs(string procLike) {
            string strSql = "select name as procName from sysobjects where xtype='P' and substring(name,1,3) not in ('dt_','sp_') and name like @procLike order by procName";
            MySqlParameter param = new MySqlParameter("@procLike", MySqlDbType.VarChar);
            param.Value = procLike;
            return GetList(strSql, new DbParameter[]{param});
        }
    }
}
